package com.movie.db.util;

import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 
* @ClassName: DataSourceUtil 
* @Description: 数据库连接关闭操作类 
* @author 左岩
* @date 2018年11月30日 上午11:02:14 
*
 */
public class DBHelper {
	private static String DRIVER_CLASS;
	private static String DB_URL;
	private static String DB_Username;
	private static String DB_Password;
	private static Properties p = new Properties();
	
	
	//注册数据库驱动
	static{
		try{
			String path = Thread.currentThread().getContextClassLoader().getResource  ("db.properties").getPath();
			FileInputStream inputStream = new FileInputStream(path);
			p.load(inputStream);
			DRIVER_CLASS=p.getProperty("driver");
			DB_URL = p.getProperty("url");
			DB_Username = p.getProperty("username");
			DB_Password = p.getProperty("password");
			
			Class.forName(DRIVER_CLASS);
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	* 
	* @Title: getConnection 
	* @Description: 返回数据库连接
	* @return Connection    返回类型 
	* @throws 
	 */
	public static Connection getConnection() 
	{
		try {
			return DriverManager.getConnection(DB_URL,DB_Username,DB_Password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * 
	* @Title: closeConnection 
	* @Description: 关闭数据库连接
	* @param @param connection
    * @throws SQLException    设定文件 
	* @return void    返回类型 
	* @throws 
	*/
	public static void 	closeConnection(Connection connection) {
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	 /**
     * 此方法可以完成  增  删  改所有的操作
     * @param sql 执行的sql语句
     * @param params 类型List<Object>
     * @return true or false  成功返回True ，失败返回False
     * 
     */
    public boolean excuteUpdate(String sql,List<Object> params){
        int res=0;//受影响的行数
        Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs=null;
        try {
            conn=getConnection();
            pstmt=conn.prepareStatement(sql);//装载sql语句
            if(params!=null){
                //加入有？占位符，在执行之前把？占位符替换掉
                for(int i=0;i<params.size();i++){
                    pstmt.setObject(i+1, params.get(i));
                }
            }
            res=pstmt.executeUpdate();
             
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(rs, pstmt, conn);
        }
        return res>0?true:false;
    }
    
    
     
    /**
     * 使用泛型方法和反射机制进行封装
     * @param sql     需要执行的sql语句
     * @param params  sql中占位符的值
     * @param cls     反射的类
     * @return List<T> List<传入的类型>
     * 
     */
    public <T> List<T> executeQuery(String sql,List<Object> params,Class<T> cls) throws Exception{
        Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs=null;
        List<T> data=new ArrayList<T>();
        try {
            conn=getConnection();
            pstmt=conn.prepareStatement(sql);//装载sql语句
            if(params!=null){
                //加入有？占位符，在执行之前把？占位符替换掉
                for(int i=0;i<params.size();i++){
                    pstmt.setObject(i+1, params.get(i));
                }
            }
            rs=pstmt.executeQuery();
            //把查询出来的记录封装成对应的实体类对象
            ResultSetMetaData rsd=rs.getMetaData();//获得列对象,通过此对象可以得到表的结构，包括，列名，列的个数，列的数据类型
            while(rs.next()){
                T m=cls.newInstance();
                for(int i=0;i<rsd.getColumnCount();i++){
                    String col_name=rsd.getColumnName(i+1);//获得列名
                    Object value=rs.getObject(col_name);//获得列所对应的值
                    Field field=cls.getDeclaredField(col_name);
                    field.setAccessible(true);//给私有属性设置可访问权
                    field.set(m, value);//给对象的私有属性赋值
                }
                data.add(m);
            }
             
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(rs, pstmt, conn);
        }
        return data;
    }
    
    
    
    /**
     *  
    * @Title: excuteQuery 
    * @Description: 查询数据并将结果返回给List<Object>
    * @param sql   
    * @param params  对象数组类型
    * @return List<Object>    返回类型 
    * @throws 
     */
    public List<Object> excuteQuery(String sql, Object[] params){   
        ResultSet rs = executeQueryRS(sql, params);  
        ResultSetMetaData rsmd = null;  
         
        int columnCount = 0;  
        try{  
            rsmd = rs.getMetaData();  
            columnCount = rsmd.getColumnCount();  
        }catch (SQLException e1) {  
            System.out.println(e1.getMessage());  
        }  
  
        List<Object> list = new ArrayList<Object>();  
  
        try{    
            while (rs.next()) {  
                Map<String, Object> map = new HashMap<String, Object>();  
                for (int i = 1; i <= columnCount; i++) {  
                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));  
                }  
                list.add(map);  
            }  
        }catch (SQLException e) {  
            System.out.println(e.getMessage());  
        }finally {   
            closeAll(rs, null, null);
        }
  
        return list;  
    }  
    
   
    /**
    * @Title: executeQueryRS 
    * @Description: SQL 查询将查询结果直接放入ResultSet中
    * @param  sql 
    * @param  params
    * @return ResultSet    返回类型数据集合
    * @throws 
    * 
     */
    public ResultSet executeQueryRS(String sql, Object[] params){  
    	Connection conn=null;
        PreparedStatement pstmt = null;
        ResultSet rs=null;
    	try{
            conn = getConnection();  
            pstmt = conn.prepareStatement(sql);  
                
            if (params != null){  
                for (int i = 0; i < params.length; i++){  
                    pstmt.setObject(i + 1, params[i]);  
                }  
            }
            
            rs = pstmt.executeQuery();  
  
        }catch (SQLException e){  
            System.out.println(e.getMessage());  
        }finally{
        	closeAll(rs, pstmt, conn);
        }
  
        return rs;  
    }
    
    
  	/**
  	 * 
  	* @Title: getSimpleObjs 
  	* @Description: 使用map处理返回的数据 
  	* @param sql 需要执行的sql语句
  	* @param params sql语句中的占位符
  	* @return List<Map<String,Object>>    返回类型 
  	* @throws 
  	 */
    public  List<Map<String,Object>> getSimpleObjs(String sql,Object[] params){
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if(params != null){
                for(int i = 0 ; i < params.length ; i++){
                    ps.setObject(i+1, params[i]);
                }
            }
            rs = ps.executeQuery();
            //精髓的地方就在这里，类ResultSet有getMetaData()会返回数据的列和对应的值的信息，然后我们将列名和对应的值作为map的键值存入map对象之中...
            ResultSetMetaData  rsmd = rs.getMetaData();
            while(rs.next()){
                Map<String,Object> map = new HashMap<String,Object>();
                for(int i = 0 ; i < rsmd.getColumnCount() ; i++){
                    String col_name = rsmd.getColumnName(i+1);
                    Object col_value = rs.getObject(col_name);
                    if(col_value == null){
                        col_value = "";
                    }
                    map.put(col_name, col_value);
                }
                mapList.add(map);
            }
            return mapList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally{
        	closeAll(rs,ps,conn);
        }
    }
    
    
    
    
    
    /**
     * 释放相应的资源
     * @param rs
     * @param pstmt
     * @param conn
     */
    public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection conn){
        try {
            if(rs!=null){
                rs.close();
            }
            if(pstmt!=null){
                pstmt.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}
